﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class KPIAttachmentController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public string Save(KPIAttachment KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateKPIAttachment";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIs.KPI.ID;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIs.KPIAssign.ID;
            command.Parameters.Add("@KPIASSIGNDETAILID", SqlDbType.VarChar).Value = KPIs.KPIAssignDetail.ID;
            command.Parameters.Add("@DOCUMENTFILENAME", SqlDbType.VarChar).Value = KPIs.DocumentFileName;
            command.Parameters.Add("@DOCUMENTNOTES", SqlDbType.VarChar).Value = KPIs.DocumentNotes;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;

            return ExecuteNonQuery(command);
        }

        public string Delete(KPIAttachment KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DeleteKPIAttachment";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;

            return ExecuteNonQuery(command);
        }

        public IList<KPIAttachment> GetKPIAttachmentByKPIAssign(int KPIAssignID, int month, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAttachments";
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;
            command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = month;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIAttachment> KPIs = new List<KPIAttachment>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAttachment p = new KPIAttachment();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.KPIAssignDetail = new KPIAssignDetailController().GetKPIAssignDetailByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignDetailID"].ToString()));
                        p.DocumentFileName = System.Configuration.ConfigurationManager.AppSettings["DownloadAttachmentPath"] + dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        p.DocumentNotes = dx.Tables[0].Rows[i]["DocumentNotes"].ToString();
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIAttachment> GetKPIAttachmentByKPIAssign_ByKpiId_KpiAssign_KpiAssignDtl(int KPIID, int KPIAssignID, int KPIAssignDetailID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAttachments_ByKpi_KpiAssign_KpiAssignDetail";
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIID;
            command.Parameters.Add("@KPIAssignID", SqlDbType.VarChar).Value = KPIAssignID;
            command.Parameters.Add("@KPIAssignDetailID", SqlDbType.VarChar).Value = KPIAssignDetailID;

            DataSet dx = GetTable(command);

            List<KPIAttachment> KPIs = new List<KPIAttachment>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAttachment p = new KPIAttachment();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.KPIAssignDetail = new KPIAssignDetailController().GetKPIAssignDetailByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignDetailID"].ToString()));
                        p.DocumentFileName = dx.Tables[0].Rows[i]["DocumentFileName"].ToString(); //System.Configuration.ConfigurationManager.AppSettings["DownloadAttachmentPath"] + dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        p.DocumentNotes = dx.Tables[0].Rows[i]["DocumentNotes"].ToString();
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIAttachment> GetKPIAttachmentByKPIAssign_ByKpiId_KpiAssign_KpiAssignDtl(int KPIAssignID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAttachments_KpiAssign";
            command.Parameters.Add("@KPIAssignID", SqlDbType.VarChar).Value = KPIAssignID;
           

            DataSet dx = GetTable(command);

            List<KPIAttachment> KPIs = new List<KPIAttachment>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAttachment p = new KPIAttachment();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        //p.KPIAssignDetail = new KPIAssignDetailController().GetKPIAssignDetailByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignDetailID"].ToString()));
                        p.DocumentFileName = dx.Tables[0].Rows[i]["DocumentFileName"].ToString(); //System.Configuration.ConfigurationManager.AppSettings["DownloadAttachmentPath"] + dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        p.DocumentNotes = dx.Tables[0].Rows[i]["DocumentNotes"].ToString();
                        //p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        //p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        //p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        //p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        //p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIAttachment> GetKPIAttachment_ByKpiId(int KpiId)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAttachments_ByKpiId";
            command.Parameters.Add("@KpiId", SqlDbType.VarChar).Value = KpiId;


            DataSet dx = GetTable(command);

            List<KPIAttachment> KPIs = new List<KPIAttachment>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAttachment p = new KPIAttachment();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        //p.KPIAssignDetail = new KPIAssignDetailController().GetKPIAssignDetailByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignDetailID"].ToString()));
                        p.DocumentFileName = dx.Tables[0].Rows[i]["DocumentFileName"].ToString(); //System.Configuration.ConfigurationManager.AppSettings["DownloadAttachmentPath"] + dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        p.DocumentNotes = dx.Tables[0].Rows[i]["DocumentNotes"].ToString();
                        //p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        //p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        //p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        //p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        //p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIChildAttachment> GetKPIChildAttachment(KPI KPI, int year, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIChildAttachments";
            command.Parameters.Add("@KPI", SqlDbType.VarChar).Value = KPI.ID;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIChildAttachment> Attachments = new List<KPIChildAttachment>();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIChildAttachment p = new KPIChildAttachment();
                        p.Bulan = dx.Tables[0].Rows[i]["Bulan"].ToString();
                        p.File = System.Configuration.ConfigurationManager.AppSettings["DownloadAttachmentPath"] + dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        //p.File =  dx.Tables[0].Rows[i]["DocumentFileName"].ToString();
                        Attachments.Add(p);
                    }
                    else
                    {
                        break;
                    }
                }
            }

            return Attachments.ToList();
        }

        public KPIAttachment GetKPIAttachmentByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAttachmentByID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            KPIAttachment KPIs = null;

            if (Data != null && Data.Count > 0)
            {
                KPIs = new KPIAttachment();
                KPIs.ID = Convert.ToInt16(Data[0].ToString());
                //KPIs.KPI = new KPIController().GetKPIByID(Convert.ToInt16(Data[1].ToString()));
                //KPIs.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(Data[2].ToString()));
                //KPIs.KPIAssignDetail = new KPIAssignDetailController().GetKPIAssignDetailByID(Convert.ToInt16(Data[3].ToString()));
                KPIs.DocumentFileName = Data[4].ToString();
                KPIs.DocumentNotes = Data[5].ToString();
                //KPIs.isActive = Convert.ToBoolean(Data[6].ToString());
                //KPIs.LastModifiedDate = Data[7] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[7].ToString());
                //KPIs.LastModifiedBy = Data[8].ToString();
                //KPIs.CreateDate = Data[9] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[9].ToString());
                //KPIs.CreateBy = Data[10].ToString();
            }

            return KPIs;
        }
    }
}